<?php
class ModelCatalogArticle extends Model {
	public function getArticle($article_id) {
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id)  WHERE p.article_id = '" . (int)$article_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
	
		return $query->row;
	}

	public function getArticles() {
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id)  WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
	
		return $query->rows;
	}
	
	public function getArticlesByCatId($cat_id, $sort = 'p.date_available', $order = 'ASC', $start = 0, $limit = 20) {
		$sql = "SELECT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) LEFT JOIN " .   DB_PREFIX . "article_to_cat p2c ON (p.article_id = p2c.article_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.cat_id = '" . (int)$cat_id . "'";
		
		$sort_data = array(
			'pd.name',
			'p.sort_order'
			
		);
			
		if (in_array($sort, $sort_data)) {
			if ($sort == 'pd.name') {
				$sql .= " ORDER BY LCASE(" . $sort . ")";
			} else {
				$sql .= " ORDER BY " . $sort;
			}
		} else {
			$sql .= " ORDER BY p.date_available";	
		}
			
		if ($order == 'DESC') {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if ($start < 0) {
			$start = 0;
		}
		
		$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
		
		$query = $this->db->query($sql);
								  
		return $query->rows;
	} 
	
	public function getTotalArticlesByCatId($cat_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "article_to_cat p2c LEFT JOIN " . DB_PREFIX . "article p ON (p2c.article_id = p.article_id)  WHERE p.status = '1' AND p.date_available <= NOW()  AND p2c.cat_id = '" . (int)$cat_id . "'");

		return $query->row['total'];
	}
	
	
	
	public function getArticlesByTag($tag, $cat_id = 0, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		if ($tag) {
		
			$sql = "SELECT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) LEFT JOIN " . DB_PREFIX . "article_tags pt ON (p.article_id = pt.article_id)  WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND (LCASE(pt.tag) = '" . $this->db->escape(strtolower($tag)) . "'";

			$keywords = explode(" ", $tag);
						
			foreach ($keywords as $keyword) {
				$sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(strtolower($keyword)) . "'";
			}
			
			$sql .= ")";
			
			if ($cat_id) {
				$data = array();
				
				$this->load->model('catalog/cat');
				
				$string = rtrim($this->getPath($cat_id), ',');
				
				foreach (explode(',', $string) as $cat_id) {
					$data[] = "cat_id = '" . (int)$cat_id . "'";
				}
				
				$sql .= " AND p.article_id IN (SELECT article_id FROM " . DB_PREFIX . "article_to_cat WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.article_id";
		
			$sort_data = array(
				'pd.name',
				'p.sort_order'
			);
				
			if (in_array($sort, $sort_data)) {
				if ($sort == 'pd.name') {
					$sql .= " ORDER BY LCASE(" . $sort . ")";
				} else {
					$sql .= " ORDER BY " . $sort;
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if ($start < 0) {
				$start = 0;
			}
		
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
			
			$query = $this->db->query($sql);
			
			$articles = array();
			
			foreach ($query->rows as $key => $value) {
				$articles[$value['article_id']] = $this->getArticle($value['article_id']);
			}
			
			return $articles;
		}
	}
	
	public function getArticlesByKeyword($keyword, $cat_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		if ($keyword) {
						
			$sql = "SELECT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id)  WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			if (!$description) {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			} else {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			}
			
			if (!$model) {
				$sql .= ")";
			} else {
				$sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
			}
			
			if ($cat_id) {
				$data = array();
				
				$this->load->model('catalog/cat');
				
				$string = rtrim($this->getPath($cat_id), ',');
				
				foreach (explode(',', $string) as $cat_id) {
					$data[] = "cat_id = '" . (int)$cat_id . "'";
				}
				
				$sql .= " AND p.article_id IN (SELECT article_id FROM " . DB_PREFIX . "article_to_cat WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.article_id";
		
			$sort_data = array(
				'pd.name',
				'p.sort_order'
			);
				
			if (in_array($sort, $sort_data)) {
				if ($sort == 'pd.name') {
					$sql .= " ORDER BY LCASE(" . $sort . ")";
				} else {
					$sql .= " ORDER BY " . $sort;
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if ($start < 0) {
				$start = 0;
			}
		
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

			$query = $this->db->query($sql);
						
			return $query->rows;
		}
		return 0;	
	}
	
	public function getTotalArticlesByKeyword($keyword, $cat_id = 0, $description = FALSE, $model = FALSE) {
		if ($keyword) {
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			if (!$description) {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			} else {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			}

			if (!$model) {
				$sql .= ")";
			} else {
				$sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
			}

			if ($cat_id) {
				$data = array();
				
				$this->load->model('catalog/cat');
				
				$string = rtrim($this->getPath($cat_id), ',');
				
				foreach (explode(',', $string) as $cat_id) {
					$data[] = "cat_id = '" . (int)$cat_id . "'";
				}
				
				$sql .= " AND p.article_id IN (SELECT article_id FROM " . DB_PREFIX . "article_to_cat WHERE " . implode(" OR ", $data) . ")";
			}
			
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			
			$query = $this->db->query($sql);
		
			if ($query->num_rows) {
				return $query->row['total'];
			} else {
				return 0;
			}
		}
		return 0;
	}
	
	public function getTotalArticlesByTag($tag, $cat_id = 0) {
		if ($tag) {
		
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) LEFT JOIN " . DB_PREFIX . "article_tags pt ON (p.article_id = pt.article_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') .  "' AND (LCASE(pt.tag) = '" . $this->db->escape(strtolower($tag)) . "'";

			$keywords = explode(" ", $tag);
						
			foreach ($keywords as $keyword) {
				$sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(strtolower($keyword)) . "'";
			}
			
			$sql .= ")";
			
			if ($cat_id) {
				$data = array();
				
				$this->load->model('catalog/cat');
				
				$string = rtrim($this->getPath($cat_id), ',');
				
				foreach (explode(',', $string) as $cat_id) {
					$data[] = "cat_id = '" . (int)$cat_id . "'";
				}
				
				$sql .= " AND p.article_id IN (SELECT article_id FROM " . DB_PREFIX . "article_to_cat WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
					
			$query = $this->db->query($sql);
			
			if ($query->num_rows) {
				return $query->row['total'];
			} else {
				return 0;
			}
		}
		return 0;
	}
	
	public function getPath($cat_id) {
		$string = $cat_id . ',';
		
		$results = $this->model_catalog_cat->getCats($cat_id);

		foreach ($results as $result) {
			$string .= $this->getPath($result['cat_id']);
		}
		
		return $string;
	}	
	
	public function getLatestArticles($limit) {
		$article_data = $this->cache->get('article.latest.' . $this->config->get('config_language_id') . '.' . $limit);

		if (!$article_data) { 
			$query = $this->db->query("SELECT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id)  WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);
		 	 
			$article_data = $query->rows;

			$this->cache->set('article.latest.' . $this->config->get('config_language_id') . '.'. $limit, $article_data);
		}
		
		return $article_data;
	}
	
	public function getPopularArticles($limit) {
		$query = $this->db->query("SELECT *, pd.name AS name, p.image FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit);
		 	 		
		return $query->rows;
	}
	
	public function getFeaturedArticles($limit) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "article_featured f LEFT JOIN " . DB_PREFIX . "article p ON (f.article_id=p.article_id) LEFT JOIN " . DB_PREFIX . "article_description pd ON (f.article_id = pd.article_id)  WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' LIMIT " . (int)$limit);
	
		return $query->rows;
	}

	public function getBestSellerArticles($limit) {
		$article_data = $this->cache->get('article.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);

		if (!$article_data) { 
			$article_data = array();
			
			$query = $this->db->query("SELECT op.article_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_article op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY op.article_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) {
				$article_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id) LEFT JOIN " . DB_PREFIX . "article_to_store p2s ON (p.article_id = p2s.article_id) WHERE p.article_id = '" . (int)$result['article_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
				
				if ($article_query->num_rows) {
					$article_data[] = $article_query->row;
				}
			}

			$this->cache->set('article.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $article_data);
		}
		
		return $article_data;
	}
		
	public function updateViewed($article_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "article SET viewed = viewed + 1 WHERE article_id = '" . (int)$article_id . "'");
	}
	
	
	public function getArticleTags($article_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "article_tags WHERE article_id = '" . (int)$article_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
	
	
	public function getArticleRelated($article_id) {
		$article_data = array();

		$article_related_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "article_related WHERE article_id = '" . (int)$article_id . "'");
		
		foreach ($article_related_query->rows as $result) { 
			$article_query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image  FROM " . DB_PREFIX . "article p LEFT JOIN " . DB_PREFIX . "article_description pd ON (p.article_id = pd.article_id)  WHERE p.article_id = '" . (int)$result['related_id'] . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
			
			if ($article_query->num_rows) {
				$article_data[$result['related_id']] = $article_query->row;
			}
		}
		
		return $article_data;
	}
	
	public function getCats($article_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "article_to_cat WHERE article_id = '" . (int)$article_id . "'");
		
		return $query->rows;
	}
	public function getDownload($download_id=0){
		
		$query = $this->db->query("SELECT name,d.download_id,filename,mask,remaining,date_added FROM " . DB_PREFIX ."download d LEFT JOIN " . DB_PREFIX ."download_description dc  ON dc.download_id=d.download_id WHERE d.download_id='" . (int)$download_id ."' AND dc.language_id = '" . (int)$this->config->get('config_language_id') . "'");
		
		return $query->row;
	}
	public function getDownloadsByArticleId($article_id,$start = 0, $limit = 20){
		if ($start < 0) {
			$start = 0;
		}
		$query = $this->db->query("SELECT name,d.download_id,filename,mask,remaining,date_added FROM " . DB_PREFIX ."download_description dc LEFT JOIN " . DB_PREFIX ."download d ON dc.download_id=d.download_id  LEFT JOIN  " . DB_PREFIX ."article_to_download  p ON d.download_id=p.download_id WHERE article_id='" . (int)$article_id ."' AND dc.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY d.date_added DESC LIMIT " . (int)$start . "," . (int)$limit);
		
		return $query->rows;
	}
	public function getTotalDownloads($article_id=0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX ."download_description dc LEFT JOIN " . DB_PREFIX ."download d ON dc.download_id=d.download_id  LEFT JOIN  " . DB_PREFIX ."article_to_download  p ON d.download_id=p.download_id WHERE article_id='" . (int)$article_id ."' AND dc.language_id = '" . (int)$this->config->get('config_language_id') . "'");
		
		return $query->row['total'];
	}
}
?>